﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.BasicList
{
    public partial class panel_Staff : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;
        private SqlConnection masterConnection;
        
        public panel_Staff()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.masterConnection = new SqlConnection(mySetting.DB2ConnectionString);

            LoadData();

            this.Staff.DoubleClick += Staff_DoubleClick;
            this.Staff.PopupMenuShowing += Staff_PopupMenuShowing;
            this.staffContext.ItemClicked += staffContext_ItemClicked;
        }

        private void callEditForm()
        {
            DataRow row = Staff.GetFocusedDataRow();
            frm_Edit_Staff editForm = new frm_Edit_Staff();
            editForm.staffid = row[0].ToString();
            if (editForm.LoadData())
            {
                //editForm.Show();
                editForm.ShowDialog();
                LoadData();
            }
            else
            {
                MessageBox.Show("Cannot load data for selected record");
            }
        }

        void Staff_DoubleClick(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        void staffContext_ItemClicked(object sender, ToolStripItemClickedEventArgs e)
        {
            this.staffContext.Hide();            
            switch (e.ClickedItem.Name)
            {
                case "importSelection":
                    importDatabase();
                    break;
                case "addSelection":
                    frm_Add_Staff add_form = new frm_Add_Staff();
                    add_form.ShowDialog();
                    LoadData();
                    break;
                case "editSelection":
                    callEditForm();
                    break;
                case "deleteSelection":
                    deleteDatabase();
                    break;
                case "refreshSelection":
                    LoadData();
                    break;
            }
        }

        void Staff_PopupMenuShowing(object sender, DevExpress.XtraGrid.Views.Grid.PopupMenuShowingEventArgs e)
        {
            this.staffContext.Show(MousePosition);
        }

        ///////////////////////////////////
        /// Load Data From Local Database
        ///////////////////////////////////
        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT [StaffID], [StaffName], [Birthday], [HireDate], [StopDate], [SecID], [SecName],"
                + " [GroupID], CASE [Sub-Group] WHEN 'MOU' THEN 'Mouter'"
                + " WHEN 'ASS' THEN 'Assembly' WHEN 'FIN' THEN 'Finish'"
                + " WHEN 'RVASS' THEN 'RV Assembly' WHEN 'RVINS' THEN 'RV Ins' WHEN 'RBARM' THEN 'Robot Arm'"
                + " WHEN 'RBINS' THEN 'Robot Ins' WHEN 'RBROT' THEN 'Robot Rotation'"
                + " WHEN 'RBZ' THEN 'Robot Z' WHEN 'SYSASS' THEN 'System Assembly'"
                + " WHEN 'SYSINS' THEN 'System Ins' WHEN 'WRVBW' THEN 'RV Box Wire'"
                + " WHEN 'WRBUB' THEN 'RB Unit Box' WHEN 'WRSW' THEN 'System Wire'"
                + " WHEN 'OFF' THEN 'Office'"
                + " ELSE [Sub-Group] END AS [Sub-Group],"
                + " [PersonalInf], [Sex], [DepartmentID], [Photo], [Education], [PhoneNumber], [Address]"
                + " FROM [222_Staff]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {
                gridStaff.DataSource = sourceDataSet.Tables[0];
                getUpdateInfo();
            }
            else
            {
                MessageBox.Show("There is no data for Staff");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("StaffID", typeof(string));
                newTable.Columns.Add("StaffName", typeof(string));
                newTable.Columns.Add("Birhtday", typeof(DateTime));
                newTable.Columns.Add("HireDate", typeof(DateTime));
                newTable.Columns.Add("StopDate", typeof(DateTime));
                newTable.Columns.Add("SecID", typeof(string));
                newTable.Columns.Add("SecName", typeof(string));
                newTable.Columns.Add("GroupID", typeof(string));
                newTable.Columns.Add("Sub-Group", typeof(string));
                newTable.Columns.Add("PersonalInf", typeof(string));
                newTable.Columns.Add("Sex", typeof(string));
                newTable.Columns.Add("DepartmentID", typeof(string));
                newTable.Columns.Add("Photo", typeof(string));
                newTable.Columns.Add("Education", typeof(string));
                newTable.Columns.Add("PhoneNumber", typeof(string));
                newTable.Columns.Add("Address", typeof(string));
                gridStaff.DataSource = newTable;
            }
            Staff.Columns[0].Caption = "Staff ID";
            Staff.Columns[1].Caption = "Staff Name";
            Staff.Columns[2].Caption = "Birthday";
            Staff.Columns[3].Caption = "Hire Date";
            Staff.Columns[4].Caption = "Stop Date";
            Staff.Columns[5].Caption = "Sec ID";
            Staff.Columns[6].Caption = "Sec Name";
            Staff.Columns[7].Caption = "Group ID";
            Staff.Columns[8].Caption = "Sub-Group";
            Staff.Columns[9].Caption = "Personal Info";
            Staff.Columns[10].Caption = "Sex";
            Staff.Columns[11].Caption = "Department ID";
            Staff.Columns[12].Caption = "Photo";
            Staff.Columns[13].Caption = "Education";
            Staff.Columns[14].Caption = "PhoneNumber";
            Staff.Columns[15].Caption = "Address";
            Staff.BestFitColumns();
        }

        /////////////////////////////////////
        /// Get Update Information
        /////////////////////////////////////
        private void getUpdateInfo()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT LastUpdate FROM [222_UpdateHistory] WHERE ID=4";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                if (dataReader.Read())
                {
                    this.updateDate.Text = dataReader["LastUpdate"].ToString();
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get update information");
            }
        }

        private void buttonRefresh_Click(object sender, EventArgs e)
        {
            LoadData();
        }


        private void buttonEdit_Click(object sender, EventArgs e)
        {
            callEditForm(); 
        }

        private void buttonImport_Click(object sender, EventArgs e)
        {
            importDatabase();
        }

        ////////////////////////////////////
        /// Update database From AS400
        ////////////////////////////////////

        private void importDatabase()
        {
            DataSet as400 = new DataSet();
            DataSet staff = new DataSet();
            string sql = null;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataTable staff_db2;
            DataTable staff_local;                
            try
            {
                //Get Data From AS400                
                masterConnection.Open();
                sql = "SELECT * FROM OPENQUERY(AS400_ZRFLIB, 'SELECT * FROM ZRKO00')";
                cmd = new SqlCommand(sql, masterConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(as400);
                staff_db2 = as400.Tables[0];
                adapter.Dispose();
                cmd.Dispose();
                masterConnection.Close(); 
                
                //Get Data From Local Database
                myConnection.Open();
                sql = "SELECT * FROM [222_Staff]";
                cmd = new SqlCommand(sql, myConnection);
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(staff, "222_Staff");
                staff_local = staff.Tables["222_Staff"];
                                
                foreach (DataRow as400_row in staff_db2.Rows)
                {
                    DataRow[] current_row = staff_local.Select("StaffID='" + as400_row["KOCD"].ToString() + "'");
                    if (current_row.Length > 0)
                    {
                        foreach (DataRow local_row in current_row)
                        {
                            local_row.BeginEdit();
                            local_row["StaffName"] = as400_row["KONAM"].ToString();
                            local_row["SecID"] = as400_row["KOBCD"].ToString();
                            local_row["SecName"] = as400_row["KOBNAM"].ToString();                            
                            local_row.EndEdit();
                        }
                    }
                    else
                    {
                        DataRow new_row = staff_local.NewRow();
                        new_row["StaffID"] = as400_row["KOCD"].ToString();
                        new_row["StaffName"] = as400_row["KONAM"].ToString();
                        new_row["SecID"] = as400_row["KOBCD"].ToString();
                        new_row["SecName"] = as400_row["KOBNAM"].ToString();                        
                        staff_local.Rows.Add(new_row);
                    }
                }  
                               

                SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);
                //Update to Local Database
                adapter.Update(staff, "222_Staff");
                
                adapter.Dispose();
                cmd.Dispose();
                myConnection.Close();
                
                //Update History
                myConnection.Open();
                sql = "UPDATE [222_UpdateHistory] SET LastUpdate=GETDATE() WHERE ID=4";
                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);                
                cmd_sql.ExecuteNonQuery();
                cmd_sql.Dispose();
                myConnection.Close();

                LoadData();

                MessageBox.Show("Database has been updated successfull.");
                                
            }
            catch (Exception ex)
            {
                if (masterConnection.State == ConnectionState.Open)
                {
                    masterConnection.Close();
                }

                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show(ex.Message);
                //MessageBox.Show("Cannot connect to AS400");
            }
        }

        private void deleteDatabase()
        {
            if (MessageBox.Show("Are you really to delete selected record now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
            {
                DataRow row = Staff.GetFocusedDataRow();
                try
                {
                    myConnection.Open();
                    string sql = "DELETE [222_Staff] WHERE StaffID='" + row[0].ToString() + "'";
                    SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("Staff deleted success.");
                    LoadData();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot delete Staff. Please try again.");
                }
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteDatabase();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            frm_Add_Staff add_form = new frm_Add_Staff();
            add_form.ShowDialog();
            LoadData();
        }
    }
}
